package com.ot.gs.util.ctd;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.StringReader;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.xml.sax.InputSource;

import com.vignette.as.client.common.AttributeDefinitionData;
import com.vignette.as.client.common.RelationData;
import com.vignette.as.client.exception.ApplicationException;
import com.vignette.as.client.exception.ValidationException;
import com.vignette.as.client.javabean.ContentType;
import com.vignette.authn.AuthnException;

public class GenerateExcelFromCtd {

	public GenerateExcelFromCtd() throws AuthnException {
		
	}

	
	/**
	 * Taking a ContentType object, create a sheet in the book.
	 * <p/>
	 * @param workbook
	 * @throws ApplicationException 
	 */
	private static void createSheetForCtd(XSSFWorkbook workbook, ContentType ctd) throws ApplicationException {
		// Create a blank sheet
		XSSFSheet sheet = workbook.createSheet(ctd.getName());
		System.out.println("created an Excel Sheet for CTD " + ctd.getName());
		
		CellStyle style = workbook.createCellStyle();
		Font font = workbook.createFont();
        font.setColor(Font.COLOR_NORMAL);
        font.setFontHeightInPoints((short)9);
        style.setFont(font);
        
		style.setWrapText(true);
		style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setBorderTop((short)1);
        style.setBorderBottom((short)1);
        style.setBorderLeft((short)1);
        style.setBorderRight((short)1);
        
        
        CellStyle headStyle = workbook.createCellStyle();
        font = workbook.createFont();
        font.setColor(Font.COLOR_NORMAL);
        font.setFontHeightInPoints((short)10);
        headStyle.setFont(font);
        
        headStyle.setWrapText(false);
        headStyle.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.index);
        headStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        headStyle.setAlignment(CellStyle.ALIGN_LEFT);
        headStyle.setBorderTop((short)1);
        headStyle.setBorderBottom((short)1);
        headStyle.setBorderLeft((short)1);
        headStyle.setBorderRight((short)1);
        
        CellStyle yellowStyle = workbook.createCellStyle();
        font = workbook.createFont();
        font.setColor(Font.COLOR_NORMAL);
        font.setFontHeightInPoints((short)10);
        yellowStyle.setFont(font);
        
        yellowStyle.setWrapText(false);
        yellowStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        yellowStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        yellowStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        yellowStyle.setAlignment(CellStyle.ALIGN_CENTER);
        yellowStyle.setBorderTop((short)1);
        yellowStyle.setBorderBottom((short)1);
        yellowStyle.setBorderLeft((short)1);
        yellowStyle.setBorderRight((short)1);
        
        
        
		// coordinates
		int rownum = 0;
		String[] objArr = new String[50];
		
		// CTD Name in cell (0,0)
		Row row = sheet.createRow(0);
		Cell cell = row.createCell(0);
		cell.setCellValue(ctd.getName());
		cell = row.createCell(1);
		cell.setCellValue("Updated at " + new Date().toString());
		
		row = sheet.createRow(2);
		cell = row.createCell(0);cell.setCellStyle(yellowStyle);
		cell.setCellValue("GENERAL");
		cell.setCellStyle(headStyle);
		
		rownum = 3;
		objArr[0] = "Content Type Name:";
		objArr[1] = ctd.getData().getDisplayName();
		writeRow(sheet, style, rownum++, objArr,  0, 2);
		
		objArr[0] = "XML Name:";
		objArr[1] = ctd.getData().getName();
		writeRow(sheet, style, rownum++, objArr,  0, 2);
		
		objArr[0] = "Description:";
		objArr[1] = ctd.getData().getDescription();
		writeRow(sheet, style, rownum++, objArr,  0, 2);
		//sheet.addMergedRegion(new CellRangeAddress(rownum-1, rownum-1, 1, 5));
		
		
		objArr[0] = "Icon File:";
		objArr[1] = ctd.getData().getIconPath();
		writeRow(sheet, style, rownum++, objArr,  0, 2);
		
		objArr[0] = "Editor Style:";
		objArr[1] = ctd.getData().getEditorStyle();
		writeRow(sheet, style, rownum++, objArr,  0, 2);
		
		objArr[0] = "Content Editor - JSP URI:";
		objArr[1] = ctd.getData().getUserApplURL();
		writeRow(sheet, style, rownum++, objArr,  0, 2);
		
		objArr[0] = "Bean Class Name:";
		objArr[1] = ctd.getData().getJavaClassName();
		writeRow(sheet, style, rownum++, objArr,  0, 2);
		
		AttributeDefinitionData[] data = ctd.getData().getAllAttributeDefinitions();
		// group by group name
		Collections.sort(Arrays.asList(data), new Comparator<AttributeDefinitionData>() {
			public int compare(AttributeDefinitionData a1, AttributeDefinitionData a2) {
				return Integer.valueOf(a1.getOrdering()).compareTo(a2.getOrdering());
		    }
		});
		
		
		
		// set cell styles first
		rownum++;
		row = sheet.createRow(rownum++);
		cell = row.createCell(0);cell.setCellStyle(headStyle);
		cell.setCellValue("Attributes");
		
		int j=0;
		sheet.setColumnWidth(j, 5000); 
		objArr[j++] = "Name"; 
		sheet.setColumnWidth(j, 9000); 
		objArr[j++] = "Description";
		sheet.setColumnWidth(j, 5000); 
		objArr[j++] = "XML Name";
		sheet.setColumnWidth(j, 4000); 
		objArr[j++] = "Bean Name";
		sheet.setColumnWidth(j, 2000); 
		objArr[j++] = "Type";
		sheet.setColumnWidth(j, 15000); 
		objArr[j++] = "Widget";
		sheet.setColumnWidth(j, 3000); 
		objArr[j++] = "Searchable";
		sheet.setColumnWidth(j, 4000); 
		objArr[j++] = "Default Label";
		sheet.setColumnWidth(j, 2000); 
		objArr[j++] = "Summary";
		sheet.setColumnWidth(j, 2000); 
		objArr[j++] = "Required";
		sheet.setColumnWidth(j, 5000); 
		objArr[j++] = "Help Text";
		sheet.setColumnWidth(j, 5000); 
		objArr[j++] = "Group Name";
		sheet.setColumnWidth(j, 6000); 
		objArr[j++] = "Table Name";
		sheet.setColumnWidth(j, 6000); 
		objArr[j++] = "Column Name";
		writeRow(sheet, yellowStyle, rownum++, objArr,  0, j);
		
		for (int i=0;i<data.length;++i) {
			j=0;
			objArr[j++] = data[i].getDisplayName();
			objArr[j++] = data[i].getComment();
			objArr[j++] = data[i].getName();
			objArr[j++] = data[i].getBeanPropertyName();
			objArr[j++] = data[i].getDataType().getDisplayName();
			
			objArr[j++] = showWidgetDetail(data[i]);
			
			objArr[j++] = String.valueOf(data[i].getSearchable());
			objArr[j++] = String.valueOf(data[i].getDefaultLabel());
			objArr[j++] = String.valueOf(data[i].getSummary());
			objArr[j++] = String.valueOf(data[i].getRequired());
			objArr[j++] = data[i].getHelpText();
			objArr[j++] = data[i].getGroupName();
			objArr[j++] = data[i].getTable();
			objArr[j++] = data[i].getColumn();
			
			writeRow(sheet, style, rownum++, objArr,  0, j);
		}
		
		//for (int i=2;i<data.length;++i) {
		//	sheet.autoSizeColumn(i);
		//}
		
		RelationData[] rData = ctd.getData().getRelation();
		rownum++;
		for (RelationData rel : rData) {
			
			// no need to do the top relation again
			if (rel.isTopRelation()) {
				continue;
			}
			
			
			
			// relation header
			rownum++;
			row = sheet.createRow(rownum++);
			cell = row.createCell(0);
			cell.setCellValue("Relation");cell.setCellStyle(headStyle);
			cell = row.createCell(1);cell.setCellStyle(headStyle);
			cell.setCellValue(rel.getDisplayName());
			
			// relation min/max rows
			objArr[0] = "Minimum # of items:";
			objArr[1] = String.valueOf(rel.getMinimum());
			writeRow(sheet, headStyle, rownum++, objArr,  0, 2);
			objArr[0] = "Maximum # of items:";
			objArr[1] = String.valueOf(rel.getMaximum());
			writeRow(sheet, headStyle, rownum++, objArr,  0, 2);
			
			j=0;
			sheet.setColumnWidth(j, 5000); objArr[j++] = "Name"; 
			sheet.setColumnWidth(j, 8000); objArr[j++] = "Description";
			sheet.setColumnWidth(j, 5000); objArr[j++] = "XML Name";
			sheet.setColumnWidth(j, 4000); objArr[j++] = "Bean Name";
			sheet.setColumnWidth(j, 2000); objArr[j++] = "Type";
			sheet.setColumnWidth(j, 15000); objArr[j++] = "Widget";
			sheet.setColumnWidth(j, 3000); objArr[j++] = "Searchable";
			sheet.setColumnWidth(j, 3000); objArr[j++] = "Default Label";
			sheet.setColumnWidth(j, 2000); objArr[j++] = "Summary";
			sheet.setColumnWidth(j, 2000); objArr[j++] = "Required";
			sheet.setColumnWidth(j, 5000); objArr[j++] = "Help Text";
			sheet.setColumnWidth(j, 5000); objArr[j++] = "Group Name";
			sheet.setColumnWidth(j, 6000); objArr[j++] = "Table Name";
			sheet.setColumnWidth(j, 6000); objArr[j++] = "Column Name";
			writeRow(sheet, yellowStyle, rownum++, objArr,  0, j);
			
			data = rel.getAttributeDefinition();
			Collections.sort(Arrays.asList(data), new Comparator<AttributeDefinitionData>() {
				public int compare(AttributeDefinitionData a1, AttributeDefinitionData a2) {
					return Integer.valueOf(a1.getOrdering()).compareTo(a2.getOrdering());
			    }
			});
			for (int i=0;i<data.length;++i) {
				j=0;
				objArr[j++] = data[i].getDisplayName();
				objArr[j++] = data[i].getComment();
				objArr[j++] = data[i].getName();
				objArr[j++] = data[i].getBeanPropertyName();
				objArr[j++] = data[i].getDataType().getDisplayName();
				
				objArr[j++] = showWidgetDetail(data[i]);
				
				objArr[j++] = String.valueOf(data[i].getSearchable());
				objArr[j++] = String.valueOf(data[i].getDefaultLabel());
				objArr[j++] = String.valueOf(data[i].getSummary());
				objArr[j++] = String.valueOf(data[i].getRequired());
				objArr[j++] = data[i].getHelpText();
				objArr[j++] = data[i].getGroupName();
				objArr[j++] = data[i].getTable();
				objArr[j++] = data[i].getColumn();
				
				writeRow(sheet, style, rownum++, objArr,  0, j);
			}
		}
	}
	
	public static Document loadXMLFromString(String xml) throws Exception
	{
	    DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
	    DocumentBuilder builder = factory.newDocumentBuilder();
	    InputSource is = new InputSource(new StringReader(xml));
	    return builder.parse(is);
	}
	
	private static String showWidgetDetail(AttributeDefinitionData data) {
		String result = data.getWidgetName() + "\n[";
		
		String detail = data.getWidgetData();
		if (detail != null && detail.trim().length()>0) {
			try {
				BufferedReader in = new BufferedReader(new StringReader(detail));
				String line;
				while ((line = in.readLine()) != null) {
					if (hasValue(line)) {
						result += line + "\n";
					}
				}
				in.close();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		
		return result+"]";
	}

	private static boolean hasValue(String line) {
		Pattern p1 = Pattern.compile("^<[^/]+/>$");
		Pattern p2 = Pattern.compile("^<[^>]+><[^/]+>$");
		Pattern p3 = Pattern.compile("^<[^>]+><!\\[CDATA\\[\\]\\]></[^>]+>");
		Matcher m1 = p1.matcher(line);
		Matcher m2 = p2.matcher(line);
		Matcher m3 = p3.matcher(line);
		
		if (!m1.matches() && !m2.matches() && !m3.matches()) {
			return true;
		} else {
			return false;
		}
		
		
	}


	/**
	 * Write one row into the sheet
	 * 
	 * @param sheet
	 * @param style
	 * @param rowNum
	 * @param objArr
	 * @param start
	 * @param length
	 */
	private static void writeRow(XSSFSheet sheet, CellStyle style, int rowNum, Object[] objArr, int start, int length) {
		Row row = sheet.createRow(rowNum);
		int cellnum = 0;
		for (int i=start;i<length;++i) {
			Cell cell = row.createCell(cellnum++);
			Object obj = objArr[i];
			if (obj instanceof String)
				cell.setCellValue((String) obj);
			else if (obj instanceof Integer)
				cell.setCellValue((Integer) obj);
			cell.setCellStyle(style);
		}
		
	}



	/**
	 * @param args
	 * @throws ValidationException 
	 * @throws ApplicationException 
	 * @throws IOException 
	 */

	public static void main(String[] args) throws ApplicationException, ValidationException, IOException {

		// Blank workbook
		XSSFWorkbook workbook = new XSSFWorkbook();
		
		// get ctd names from a text file
		BufferedReader in = new BufferedReader(new FileReader("contentTypes.txt"));
		try {
			String line;
			while ((line = in.readLine()) != null) {
				if (line.trim().length()<=0 || line.trim().startsWith("#")) continue;
				
				// for each content type
				ContentType ctd = (ContentType) ContentType.findByName(line.trim());
			
				System.out.println("generate sheet for CTD " + ctd.getName());
				createSheetForCtd(workbook, ctd);
			}
		} finally {
			in.close();
		}
		
		try {
			// Write the workbook in file system
			FileOutputStream out = new FileOutputStream(new File(
					"ContentType-Design.xlsx"));
			workbook.write(out);
			out.close();
			System.out
					.println("ContentType-Design.xlsx written successfully on disk.");
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
}
